Correct syntax in stored procedure and method using MsSqlProvider.ExecProcedure? [migrated]

Posted by Dudi on Programmers See other posts from Programmers or by Dudi
Published on 2012-07-06T15:10:49Z Indexed on 2012/07/06 15:22 UTC
Read the original article Hit count: 124

Filed under:
|

I have problem with ASP.net and database prcedure

My procedure in mssql base

USE [dbase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[top1000]
            @Published datetime output,
            @Title nvarchar(100) output,
            @Url nvarchar(1000) output,
            @Count INT output

AS 
SET @Published = (SELECT TOP 1000  dbo.vst_download_files.dfl_date_public FROM dbo.vst_download_files
    ORDER BY dbo.vst_download_files.dfl_download_count DESC )
SET @Title = (SELECT TOP 1000  dbo.vst_download_files.dfl_name FROM dbo.vst_download_files
    ORDER BY dbo.vst_download_files.dfl_download_count DESC) 
SET @Url = (SELECT TOP 1000  dbo.vst_download_files.dfl_source_url FROM dbo.vst_download_files
    ORDER BY dbo.vst_download_files.dfl_download_count DESC) 
SET @Count = (SELECT TOP 1000  dbo.vst_download_files.dfl_download_count FROM dbo.vst_download_files
    ORDER BY dbo.vst_download_files.dfl_download_count DESC) 

And my proceduer in website project

public static void Top1000()
    {

        List<DownloadFile> List = new List<DownloadFile>();
        SqlDataReader dbReader;

        SqlParameter published = new SqlParameter("@Published", SqlDbType.DateTime2);
        published.Direction = ParameterDirection.Output;
        SqlParameter title = new SqlParameter("@Title", SqlDbType.NVarChar);
        title.Direction = ParameterDirection.Output;
        SqlParameter url = new SqlParameter("@Url", SqlDbType.NVarChar);
        url.Direction = ParameterDirection.Output;
        SqlParameter count = new SqlParameter("@Count", SqlDbType.Int);
        count.Direction = ParameterDirection.Output;
        SqlParameter[] parm = {published, title, count};


        dbReader = MsSqlProvider.ExecProcedure("top1000", parm);


        try
        {
            while (dbReader.Read())
            {
                DownloadFile df = new DownloadFile();
                //df.AddDate = dbReader["dfl_date_public"];
                df.Name = dbReader["dlf_name"].ToString();
                df.SourceUrl = dbReader["dlf_source_url"].ToString();
                df.DownloadCount = Convert.ToInt32(dbReader["dlf_download_count"]);
                List.Add(df);
            }


            XmlDocument top1000Xml = new XmlDocument();
            XmlNode XMLNode = top1000Xml.CreateElement("products");


            foreach (DownloadFile df in List)
            {
                XmlNode productNode = top1000Xml.CreateElement("product");

                XmlNode publishedNode = top1000Xml.CreateElement("published");
                publishedNode.InnerText = "data dodania";
                XMLNode.AppendChild(publishedNode);

                XmlNode titleNode = top1000Xml.CreateElement("title");
                titleNode.InnerText = df.Name;
                XMLNode.AppendChild(titleNode);
            }
            top1000Xml.AppendChild(XMLNode);
            top1000Xml.Save("\\pages\\test.xml");

        }
        catch
        {

        }
        finally
        {
            dbReader.Close();

        }

    }

And if I made to MsSqlProvider.ExecProcedure("top1000", parm); I got String[1]: property Size has invalid size of 0.

Where I shoudl look for solution? Procedure or method?

© Programmers or respective owner

Related posts about ASP.NET

Related posts about mssql